Notes

Updates

  • Grant and I now have network access to both Munis and Artifax.
  • Artifax data will be good only for MCM and MHC for 2013 and later.

Goal

For each GL revenue account:

. .
FTS Admissions FTS Programs
KLF Admissions KLF Programs
KLF School Groups KLF Workshops
MCM Admissions MCM Camps
MCM Programs MHC Pgm Generic
Development Event Revenue Kansas City Getaway
MHC Admissions MHC Pgm Adults
MHC Pgm Crawls MHC Pgm Groups
MHC Pgm GRP MHC Pgm History Forum
MHC Pgm History of Hip MHC Pgm Library
MHC Pgm Other MHC Pgm Tuesday Talks
MHC Pgm Whats up doc MHC Pgm Wkp
MHC Pgm WW1 MHC Pgm Young Adult
North Shore Getaway SRL Admissions
SRL School Groups SRL Workshops

…generate monthly, scenario-parameterized revenue forecasts.

Subgoals

  • Generate baseline revenue forecasts using only past revenue actuals.
  • Forecast revenue components separately, viz. prices and ticket sales.
  • Establish relationships between revenue and behaviors.
  • Carry out scenario analysis.

Tasks

  • Omit unrepresentative data.
  • Munis:
    • Go spelunking through Munis to find detailed revenue account data.
    • Analyze Munis data.
  • Archibus:
    • Go spelunking through Artifax to find detailed account data.
    • Analyze Artifax data.
  • Clean up code and refactor copypasta.
  • Figure out a better R -> Octave -> R workflow.

Introduction

  • Last time: top-down; this time: bottom-up
  • We’ll work with Tess revenue in the MCM Admissions GL Account only.
  • We’re going to make the best forecast we can based on historical revenue variation and prior information about seasonality.
  • We’ll model daily revenue and sum the predictions to produce monthly forecasts.
  • Then we’ll try to apply it other GL accounts.

Mill City Admissions Revenue Analysis

Exploratory Analysis: Periodicity

First, a slight digression: Mill City Admissions Revenue by month, day, and hour

I wanted to throw in what might be a really useful, hour-level plot before summing over the hours and proceeding to day-level analysis.

Hourly averages are in black, actuals are in grey.

Daily transaction totals

Daily transaction totals: year-on-year

Daily transaction totals for each month:

Daily transaction totals: week-on-week for each month

Observations

  • Not surpisingly, we can see a yearly cycle and a weekly cycle.
  • There’s apparently little cycle-to-cycle varation in this set; timeseries forecasts could be pretty reliable.

Time Series Model: Revenue

Assumptions (non-technical)

  • Daily transaction totals have three components:
    • a yearly periodic pattern
    • a weekly periodic pattern, and
    • white noise.

Model the yearly pattern:

Include the weekly pattern:

Generate one-year, daily forecast:

Generate one-year, monthly forecast:

Monthly transaction totals (dots) with predictions, and uncertainty (68% and 95% predictive intervals) in blue:

Year Month 95% Worst-Case Forecast 95% Best-Case
2018 2 19157.62 22637.78 26117.94
2018 3 37273.93 40938.57 44603.21
2018 4 37555.32 41151.78 44748.24
2018 5 40281.80 43937.69 47593.57
2018 6 52907.79 56504.23 60100.67
2018 7 82512.19 86168.08 89823.97
2018 8 78651.60 82307.48 85963.37
2018 9 46803.21 50399.65 53996.10
2018 10 43068.44 46724.33 50380.22
2018 11 32815.90 36412.33 40008.77
2018 12 22892.19 26548.09 30203.98
2019 1 19672.18 23328.08 26983.98
2019 2 19055.02 22534.93 26014.84
2019 3 39244.39 42909.27 46574.16
2019 4 34934.85 38531.31 42127.76
2019 5 40818.01 44473.90 48129.78
2019 6 54809.38 58405.82 62002.26
2019 7 80342.93 83998.82 87654.71
2019 8 67193.23 70476.31 73759.40

Observations and possibilities for model improvement

Clearly, using other sources of data than just historical totals would be a huge improvement, but the discussion in this section leaves that aside for now.

  • It’s very doubtful that the yearly cycle is as stable as this model assumes (i.e. this model is definitely overfit).
  • A Gaussian likelihood function is not really appropriate for these data. Fitting seperate hurdle models for income and refunds and then adding them would be better.
  • Special days, e.g. holidays, should be modeled.

Interlude: Where Next?

Some possible next steps I can think of:

  • generate similar forecasts for other GL Accounts in Tess. That way we could at least have some baselines at our disposal ASAP even if we can’t do scenario analysis with them.
  • move further in the direction of the material in the next sections.
  • look at other variables and hunt for deeper relationships.

Exploratory Analysis: Sales and Prices

Daily MCM Admissions revenue for each by Price Type:

Effective Ticket price through the dataset

These plots aren’t especially relevant but I’ve left them in because they’re kind of cool looking.

Removing groups and restricting the range from ~$-10 to ~$40:

We’ll focus on Adult admissions from here on out.

MCM Daily Attendance Avg. Price and Count Decomposition:

Time Series Model: Transaction Counts

If we have a forecast of the transaction count, we can try out different (avg.) pricing scenarios as well as answer questions like, “What if I increased my admissions transaction count for adults by 10%?”

Daily Transaction Count Forecast

Let’s make a silly demand curve to get a flavor for what we can do with this.

Here’s some discussion on this:

http://www.museummarketing.info/2008/08/price-elasticity-optimum-entrance-fee-part-3/

We’ll use their demand curve. According to:

http://www.macrotrends.net/2548/euro-dollar-exchange-rate-historical-chart

…the exchange rate in Aug 2008 was about 1.4 dollars per euro.

IMPORTANT NOTE: THIS CURVE IS ALMOST CERTAINLY WAY OFF; WHAT FOLLOWS IS PROOF-OF-CONCEPT

Ratio of the transaction count expected at a new price to the transaction count at the current price

Daily transaction count forecast at different avg. ticket rates

Daily revenue forecast at different avg. ticket rates

Monthly revenue forecast at different avg. ticket rates